package com.example.mapper;

import com.example.pojo.Record;
import org.apache.ibatis.annotations.*;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Mapper
public interface RecordMapper {
    // 多条件查询订单记录
    @Select("<script>" +
            "SELECT r.*, g.name as goods_name, s.name as seller_name, c.name as buyer_name, gc.name as cate_name " +
            "FROM record r " +
            "LEFT JOIN goods g ON r.goods_id = g.id " +
            "LEFT JOIN student s ON r.stu_id = s.id " +
            "LEFT JOIN student c ON r.cus_id = c.id " +
            "LEFT JOIN `goods-cate` gc ON r.cate_id = gc.id " +
            "<where>" +
            "   <if test='sellerName != null and sellerName != \"\"'>AND s.name LIKE CONCAT('%', #{sellerName}, '%')</if>" +
            "   <if test='customerName != null and customerName != \"\"'>AND c.name LIKE CONCAT('%', #{customerName}, '%')</if>" +
            "   <if test='goodsName != null and goodsName != \"\"'>AND g.name LIKE CONCAT('%', #{goodsName}, '%')</if>" +
            "   <if test='cateId != null'>AND r.cate_id = #{cateId}</if>" +
            "</where>" +
            "ORDER BY r.create_time DESC" +
            "</script>")
    List<Record> list(@Param("sellerName") String sellerName,
                      @Param("customerName") String customerName,
                      @Param("goodsName") String goodsName,
                      @Param("cateId") Integer cateId);

    // 查询订单详情
    @Select("SELECT r.*, g.name as goods_name, s.name as seller_name, c.name as buyer_name, gc.name as cate_name " +
            "FROM record r " +
            "LEFT JOIN goods g ON r.goods_id = g.id " +
            "LEFT JOIN student s ON r.stu_id = s.id " +
            "LEFT JOIN student c ON r.cus_id = c.id " +
            "LEFT JOIN `goods-cate` gc ON r.cate_id = gc.id " +
            "WHERE r.id = #{id}")
    Record findById(Integer id);

    // 新增统计SQL
    @Select("SELECT gc.name as cate_name, COUNT(r.id) as trade_count, SUM(r.price) as total_amount " +
            "FROM record r " +
            "LEFT JOIN `goods-cate` gc ON r.cate_id = gc.id " +
            "WHERE r.create_time BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY r.cate_id")
    List<Map<String, Object>> statistics(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

}